inserting function to excel cell throws "OLE method failed"

So I'm trying to make a hyperlink out of an object's URL, but setting a cell to have anything with a "=" at the beginning throws the error:

CRCRD5311W: OLE method failed:
OLE problem: , OLE error code: -2146827284

Here's my setCell method

void setCell(int row, int col, string s) {
    closeIfNonNull objCell
    clear(args)
    put(args, (intToCol col) row "")
    checkRes(oleGet(objSheet, cMethodRange,args, objCell))

    if (null objCell) {
    ack "Unable to get cell object"
        halt
    }

        // Excel considers string starting with an equal character (=) to be an excel formula
        // hence it throws an error "OLE method failed". To avoid this we append "'" before the string.
        if( s[0:0] == "=")
        {
                s = "'" s
        }
    checkRes(olePut(objCell, cPropertyValue, s))
}

Re: inserting function to excel cell throws "OLE method failed"
llandale - Mon Jun 16 16:22:55 EDT 2014

Don't know. 

Is the format of the cell such that it CAN take a string? 
Try property "Value2" instead of "Value"

-Louie

Re: inserting function to excel cell throws "OLE method failed"
dybdalk - Mon Jun 16 16:35:33 EDT 2014

It works perfectly fine as long as I have this bit of code in there

// Excel considers string starting with an equal character (=) to be an excel formula
// hence it throws an error "OLE method failed". To avoid this we append "'" before the string.
if( s[0:0] == "="){
   s = "'" s
}

This prevents it from thinking that the string is a formula when it gets put into excel, but I  would in fact like it to be read as a formula, just without throwing the aforementioned error

Re: inserting function to excel cell throws "OLE method failed"
llandale - Mon Jun 16 16:51:46 EDT 2014

// Excel considers string starting with an equal character (=) to be an excel formula
// hence it throws an error "OLE method failed". To avoid this we append "'" before the string.
if( s[0:0] == "="){
   s = "'" s
}

What does "s" look like?

print s

-Louie

Re: inserting function to excel cell throws "OLE method failed"
dybdalk - Mon Jun 16 17:10:54 EDT 2014

ideally, s is a formula. for example, s could be "=HYPERLINK(\"http://www.google.com\",\"a link to google\")"

Re: inserting function to excel cell throws "OLE method failed"
dybdalk - Mon Jun 16 18:12:18 EDT 2014

I figured it out! s was just far too long for excel to handle, given the hyperlink i had given it. I shortened things up a bit, and it all works fine

Re: inserting function to excel cell throws "OLE method failed"
llandale - Tue Jun 17 13:47:03 EDT 2014

If you provide the length that failed and the length that worked, maybe we can learn something.  Could you physically paste the long version directly into the cell, without using DXL?

I'm suspecting that the cell had format "text", which indeed has a limit on length; as far as I can tell.

-Louie

Re: inserting function to excel cell throws "OLE method failed"
dybdalk - Tue Jun 17 14:07:23 EDT 2014

Actually, I think the problem was that I was trying to insert things into the cell after the hyperlink. here's an example of a string that fails:

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\") (" cellClassVal ", " cellAcceptedVal ")"

where cellClassVal and cellAcceptedVal are just 6-10 char strings pulled from attributes. and here's what ends up working:

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\")"

My assumption is that the first one fails because Excel doesn't want anything other than that first hyperlink function put in the cell.

Re: inserting function to excel cell throws "OLE method failed"
llandale - Tue Jun 17 15:29:33 EDT 2014

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\") (" cellClassVal ", " cellAcceptedVal ")"

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\")"

I does appear you want those small attr-values displayed as part of the Hyperlink text, adjacent to the object identifer.  I think that is this:

• cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) ") (" cellClassVal ", " cellAcceptedVal ")\""

I ran code and printed cellStr, opened Excel and copied pasted.

• =HYPERLINK("blablablaURLinfo","MyMod_6") (extra, attrValues)
• =HYPERLINK("blablablaURLinfo","MyMod_6")

Both pasted well into regular "Text" formatted cells, into "General" cells, and into special "Text" cells.  Only the General cells however displayed the 2nd one as an actual hyperlink.

I notice that after pasting a valid hyperlink, Excel would not let me add extra characters to the end.

Oh well, learned a little about Excel I guess.

Re: inserting function to excel cell throws "OLE method failed"
Wolfgang Uhr - Sat Jun 21 10:52:39 EDT 2014

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\") (" cellClassVal ", " cellAcceptedVal ")"

cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\")"

Hello

> cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\") (" cellClassVal ", " cellAcceptedVal ")"

That is a wrong excel formula. Try the following

> cellStr = "=HYPERLINK(\"" objUrl "\",\"" identifier (destObj1) "\")" & "(" cellClassVal ", " cellAcceptedVal ")"

Best regards

Wolfgang Uhr